package com.xiyili.topspecialty.data;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SpecialtyDatabaseHelper extends SQLiteOpenHelper {

	//
	private static String DB_NAME = "xk_all.db";
	private static final String TABLE_NAME = "specialties";
	private static final String FINE_COURSES_TABLE_NAME = "fine_courses";



	private Context mContext;

	private SQLiteDatabase mDb;

	private static SpecialtyDatabaseHelper _instance;
	private String db_path = null;

	private SpecialtyDatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		mContext = context;
		db_path = context.getDatabasePath(DB_NAME).getAbsolutePath();
		openDatabase();
	}

	public static SpecialtyDatabaseHelper getInstance(Context context) {
		if(_instance == null) {
			_instance = new SpecialtyDatabaseHelper(context);
		}
		return _instance;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	private void createDatabase() throws IOException {

		boolean dbExist = checkDatabase();

		if (dbExist) {
			// do nothing - database already exist
		} else {

			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();

			try {

				copyDatabase();

			} catch (IOException e) {

				throw new Error("Error copying database");

			}
		}

	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDatabase() {

		SQLiteDatabase checkDB = null;

		try {
			checkDB = SQLiteDatabase.openDatabase(db_path, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (Exception e) {
			// database does't exist yet.
			Log.d("checkDatabase", e.getMessage());
		}

		if (checkDB != null) {

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDatabase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = mContext.getAssets().open(DB_NAME);

		// Path to the just created empty db

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(db_path);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	private void openDatabase() throws SQLException {
		// if needed create database
		try {
			createDatabase();
		} catch (IOException e) {
			Log.e("openDatabase", e.getMessage());
		}

		// Open the database
		mDb = SQLiteDatabase.openDatabase(db_path, null,
				SQLiteDatabase.OPEN_READONLY);

	}

	@Override
	public synchronized void close() {

		if (mDb != null)
			mDb.close();

		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	// Add your public helper methods to access and get content from the
	// database.
	// You could return cursors by doing "return myDataBase.query(....)" so it'd
	// be easy
	// to you to create adapters for your views.

	/**
	 * <pre>
	 * 06-17 12:03:43.360: E/AndroidRuntime(10783):
	 * android.database.sqlite.SQLiteException: near "LIKE": syntax error (code 1): , while compiling:
	 * SELECT DISTINCT code, name FROM specialties WHERE  name LIKE

	 * <pre>
	 * @return
	 */


	public List<SpecialtyItem> findAllSpecialtyItem(){
		Cursor cursor = mDb.query(true, TABLE_NAME, new String[] {"code","name"}, null, null, null, null, null, null);
		return buildSpecialtyItemList(cursor);
	}

	public SpecialtyItem findSpecialtyItemByName(String specialty) {
		Cursor cursor = mDb.query(true, TABLE_NAME, new String[] {"code","name"}, "name=?", new String[] {specialty}, null, null, null, " 1 ");
		List<SpecialtyItem> items = buildSpecialtyItemList(cursor);
		if(items.size() > 0) {
			return items.get(0);
		}else {
			return null;
		}
	}

	public List<SpecialtyItem> searchSpecialtyItemByName(String name){
		Cursor cursor = mDb.query(true,
				TABLE_NAME,
				new String[] {"code","name"},
				" name LIKE ?",
				new String[] {"%"+name+"%"},
				null,
				null,
				null,
				null);
		return buildSpecialtyItemList(cursor);
	}

	public List<School> findAllSchool() {
		Cursor cursor = mDb.query(true,
				TABLE_NAME,
				new String[] {"school_code","school_name"},
				null,
				null,
				null, null, null, null);

		return buildSchoolList(cursor);
	}
	public List<School> searchSchoolByName(String name) {
		Cursor cursor = mDb.query(true,TABLE_NAME,
				new String[] {"school_code","school_name"},
				" school_name LIKE ? ",
				new String[] {"%"+name+"%"},
				null, null, null, null);

		return buildSchoolList(cursor);
	}



	public List<Specialty> findSpecialtyByCode(String code){
		Cursor cursor = mDb.query(TABLE_NAME, null, "code=?", new String[] {code}, null, null, null,null);
		return buildList(cursor);
	}
	public List<Specialty> findSpecialtyBySchoolCode(String code){
		Cursor cursor = mDb.query(TABLE_NAME, null, "school_code=?", new String[] {code}, null, null, "score DESC ",null);
		return buildList(cursor);
	}


	private List<SpecialtyItem> buildSpecialtyItemList(Cursor cursor) {
		List<SpecialtyItem> list = new ArrayList<SpecialtyItem>();
		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					int codeIndex = cursor.getColumnIndex("code");
					int nameIndex = cursor.getColumnIndex("name");
					do {
						SpecialtyItem item = new SpecialtyItem(cursor.getString(codeIndex),
								cursor.getString(nameIndex));
						list.add(item);
					} while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cursor.close();
			}
		}
		return list;
	}
	private List<School> buildSchoolList(Cursor cursor) {
		List<School> list = new ArrayList<School>();
		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					int codeIndex = cursor.getColumnIndex("school_code");
					int nameIndex = cursor.getColumnIndex("school_name");
					do {
						School item = new School(cursor.getString(codeIndex),
								cursor.getString(nameIndex));
						list.add(item);
					} while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cursor.close();
			}
		}
		return list;
	}



	private List<Specialty> buildList(Cursor cursor ){
		List<Specialty> list = new ArrayList<Specialty>();
		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					int codeIndex = cursor.getColumnIndex("code");
					int nameIndex = cursor.getColumnIndex("name");
					int schoolNameIndex = cursor.getColumnIndex("school_name");
					int schoolCodeIndex = cursor.getColumnIndex("school_code");
					int scoreIndex = cursor.getColumnIndex("score");
					do {
						String code =cursor.getString(codeIndex);
						String name =cursor.getString(nameIndex);
						String schoolName = cursor.getString(schoolNameIndex);
						String schoolCode  = cursor.getString(schoolCodeIndex);
						int score = cursor.getInt(scoreIndex);
						Specialty item = new Specialty(code,name,schoolCode,schoolName,score);
						list.add(item);
					} while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cursor.close();
			}
		}
		return list;
	}
	private List<FineCourse> buildFineCourseList(Cursor cursor ){
		List<FineCourse> list = new ArrayList<FineCourse>();
		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					int specialtyIndex = cursor.getColumnIndex("specialty");
					int nameIndex = cursor.getColumnIndex("name");
					int schoolNameIndex = cursor.getColumnIndex("school_name");
					int teacherIndex = cursor.getColumnIndex("teacher");
					do {
						String specialty =cursor.getString(specialtyIndex);
						String name =cursor.getString(nameIndex);
						String schoolName = cursor.getString(schoolNameIndex);
						String teacher  = cursor.getString(teacherIndex);
						FineCourse item = new FineCourse(specialty,name,schoolName,teacher);
						list.add(item);
					} while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cursor.close();
			}
		}
		return list;
	}

	/**
	 * 06-18 00:06:25.410: E/AndroidRuntime(4970): android.database.sqlite.SQLiteException:
	 * no such table: fine_courses (code 1): , while compiling: SELECT DISTINCT specialty FROM fine_courses

	 * @return
	 */
	public List<String> findAllFineCourseCategories() {
		List<String> list = new ArrayList<String>();

		Cursor cursor = mDb.query(true, FINE_COURSES_TABLE_NAME	, new String[] {"specialty"}, null,null,null,null,null,null);

		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {
					int specialtyIndex = cursor.getColumnIndex("specialty");
					do {

						list.add(cursor.getString(specialtyIndex));
					} while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				cursor.close();
			}
		}
		return list;
	}

	public  List<FineCourse> findFindCoursesByCategory(String category) {
		Cursor cursor = mDb.query(FINE_COURSES_TABLE_NAME, null, " specialty = ?", new String[] {category}, null, null,null);
		return buildFineCourseList(cursor);
	}

	public List<FineCourse> findFindCoursesBySchoolName(String schoolName) {
		Cursor cursor = mDb.query(FINE_COURSES_TABLE_NAME, null, " school_name = ?", new String[] {schoolName}, null, null,null);
		return buildFineCourseList(cursor);
	}






}
